package cn.chinaunicom.sdsi.demo;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

public class TestCreateExcel {
    /**
     * @Description:excel导出
     * @return
     * @author: daiyw
     * @time:2017年3月14日
     */
    public void testExcel() throws IOException {
        String[] titles = { "姓名", "年龄", "日期" };
        String[] d = { "张三", "10", "2017-01-16" };
        String[] e = { "张四", "110", "2017-01-17" };
        List<Object[]> datas = new ArrayList<Object[]>();
        datas.add(e);
        datas.add(d);
        String filename = "kkkk";
        FileOutputStream out = new FileOutputStream("E:/" + filename + ".xlsx");
        Workbook wb = createExcel(titles, datas);
        wb.write(out);
        wb.close();
        out.close();
    }

    private Workbook createExcel(String[] titles, List<Object[]> datas) {
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sh = wb.createSheet();
        int currentRowNum = 1;// 从第几行开始写
        int currentColNum = 0;// 从第几列开始写
        for (Object objects[] : datas) {// 遍历list
            Row row = sh.createRow(currentRowNum);// 一个list就是一行，创建一个行
            for (Object object : objects) {// 遍历每一个list的Object[]数组
                if (object == null) {
                    Cell cell = row.createCell(currentColNum++, SXSSFCell.CELL_TYPE_BLANK);// 如果该单元格为空，则创建一个空格子，不然数据会移位
                    cell.setCellValue("");// 将这个空格设置为空字符串
                } else if (object instanceof Double || object instanceof Float) {// 判断这个格子放的数据的类型，其他的同理
                    Cell cell = row.createCell(currentColNum++, SXSSFCell.CELL_TYPE_NUMERIC);// 如果是Double或者Float类型的，则用这个方式
                    cell.setCellValue(Double.parseDouble(object.toString()));
                } else if (object instanceof Long || object instanceof Integer) {
                    Cell cell = row.createCell(currentColNum++, SXSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(Double.parseDouble(object.toString()));
                } else if (object instanceof Date) {
                    Cell cell = row.createCell(currentColNum++, XSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(new SimpleDateFormat("dd-MM-yyyy").format((Date) object));
                } else if (object instanceof Boolean) {
                    Cell cell = row.createCell(currentColNum++, SXSSFCell.CELL_TYPE_BOOLEAN);
                    cell.setCellValue((Boolean) object);
                } else {
                    Cell cell = row.createCell(currentColNum++, SXSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(String.valueOf(object));
                }

            }
            currentRowNum++;// 写完第一个list，跳到下一行
            currentColNum = 0;
        }
        Row row = sh.createRow(0);
        for (int cellnum = 0; cellnum < titles.length; cellnum++) {
            Cell cell = row.createCell(cellnum);
            cell.setCellValue(titles[cellnum]);
        }
        return wb;
    }

}
